package com.Dao;

import com.bean.Article;
import com.tools.DButil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ArticleDao {
    private Article article = null;
    //添加文章
    public void addArticle(Article article)throws SQLException{
        String sql="insert into article(origin,pubtime,content,title,typename,picture) values (?,?,?,?,?,?)";
        QueryRunner runner=new QueryRunner(DButil.getDataSource());
        runner.update(sql,article.getCount(),article.getPubtime(),article.getContent(),article.getTitle(),article.getTypename(),article.getPicture());
    }
    //删除文章
    public void deleteArticleByTitle(String title) throws SQLException {
        String sql="delete from article where title=?";
        QueryRunner runner=new QueryRunner(DButil.getDataSource());
        runner.update(sql,title);
    }
    //查询类别中的条数
    public  int querycount(String typename) throws SQLException {
        String sql="select count(title) from article where typename=?";
        QueryRunner runner=new QueryRunner(DButil.getDataSource());
        Long c=(Long) runner.query(sql,new ScalarHandler(),typename);
        int count=c.intValue();
        return count;
    }

    //搜查
    //获取所有文章
    public List<Article> getAllArticle() throws SQLException {
        String sql = "select * from article order by pubtime desc ";//根据时间降序，limit0，10——是指分页，10个数据一页
        QueryRunner runner = new QueryRunner(DButil.getDataSource());
        return runner.query(sql, new BeanListHandler<Article>(Article.class));
    }

    //获取所有类型中文章的数量
    public int findAllCount(String articletype) throws SQLException {
        String sql="select count(*) from article";
        QueryRunner runner=new QueryRunner(DButil.getDataSource());

        //如果不是查询全部的数据
        if(!"全部".equals(articletype)){
            sql+="where articletype=?";
            Long count=(Long) runner.query(sql,new ScalarHandler(),articletype);
            return count.intValue();
        }else {
            Long count=(Long) runner.query(sql,new ScalarHandler());
            return count.intValue();
        }
    }
    //按类型名查找文章
    public  Article findArticleByTitle(String title) throws SQLException {
        String sql="select * from article where title=?";
        QueryRunner runner = new QueryRunner(DButil.getDataSource());
        List<Article> rs = runner.query(sql, new BeanListHandler<Article>(Article.class),title);
        if (rs != null) {
            for (int i = 0; i < rs.size(); i++) {
                 //获取文章信息
                article = new Article();
                article.setId(rs.get(i).getId());
                article.setTypename(rs.get(i).getTypename());
                article.setTitle(rs.get(i).getTitle());
                article.setContent(rs.get(i).getContent());
                article.setOrigin(rs.get(i).getOrigin());
                article.setPubtime(rs.get(i).getPubtime());
                article.setCount(rs.get(i).getCount());
                //article.setRemark(rs.get(i).getRemark());

                //  查询tb_review数据表统计当前文章的评论数
                sql = "select count(id) from remark where articleid=" + article.getId();
                QueryRunner runner1 = new QueryRunner(DButil.getDataSource());
                Long c=(Long) runner1.query(sql,new ScalarHandler());
                int count=c.intValue();
                if (count != 0) {
                    article.setRemark(count);
                }
            }
        }
        return article;
    }

    public  List<Article> findArticleByType(String typeName) throws SQLException {
        String sql="select * from article where typeName=?";
        QueryRunner runner=new QueryRunner(DButil.getDataSource());
        return (List<Article>) runner.query(sql, new BeanHandler<Article>(Article.class),typeName);
    }
    public  Article findArticleById(int id) throws SQLException {
        String sql="select * from article where id=?";
        QueryRunner runner=new QueryRunner(DButil.getDataSource());
        return  runner.query(sql, new BeanHandler<Article>(Article.class),id);
    }


    //查询文章
    public List queryArticle(String typename, String type) throws SQLException {
        List articlelist = new ArrayList();
        String sql = "";
        if (typename == "")                    //不按类别查询，查询出前3条记录
            sql = "select * from article order by pubtime DESC limit 0,3";
        else                            //按类别查询
            if (type == null || type.equals("") || !type.equals("all"))
                sql = "select * from article where typename='" + typename + " 'order by pubtime DESC limit 0,5";
            else
                sql = "select * from article where typename='" + typename + " 'order by pubtime DESC";
        //ResultSet rs = connection.executeQuery(sql);
        QueryRunner runner = new QueryRunner(DButil.getDataSource());
        List<Article> rs = runner.query(sql, new BeanListHandler<Article>(Article.class));
        if (rs != null) {
            try {
                for (int i = 0; i < rs.size(); i++) {
                    /* 获取文章信息 */
                    article = new Article();
                    article.setId(rs.get(i).getId());
                    article.setTypename(rs.get(i).getTypename());
                    article.setTitle(rs.get(i).getTitle());
                    article.setContent(rs.get(i).getContent());
                    article.setOrigin(rs.get(i).getOrigin());
                    article.setPubtime(rs.get(i).getPubtime());
                    article.setCount(rs.get(i).getCount());
//                    article.setRemark(rs.get(i).getRemark());

                    /* 查询tb_review数据表统计当前文章的评论数 */
                    sql="select count(id) from remark where articleid="+article.getId();
                    QueryRunner runner1 = new QueryRunner(DButil.getDataSource());
                    Long c=(Long) runner1.query(sql,new ScalarHandler());
                    int count=c.intValue();
                    if (count != 0) {
                        article.setRemark(count);
                    }
                    articlelist.add(article);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return articlelist;
    }
    //操作文章
    public boolean operationArticle(String oper, Article single) throws SQLException {
        /* 生成SQL语句 */
        String sql = null;
        if (oper.equals("readTimes"))			//累加阅读次数
            sql = "update article set count=count+1 where id="+ single.getId();

        /* 执行SQL语句 */
        QueryRunner runner=new QueryRunner(DButil.getDataSource());
        int flag =runner.update(sql);
        if(flag>0)
            return true;
        else
            return false;

    }

    //查询单篇文章
    public Article queryArticleSingle(int id) throws SQLException {
        String sql = "select * from article where id='" + id + "'";
        //ResultSet rs = connection.executeQuery(sql);
        QueryRunner runner = new QueryRunner(DButil.getDataSource());
        List<Article> rs = runner.query(sql, new BeanListHandler<Article>(Article.class));
        if (rs != null) {
            for (int i = 0; i < rs.size(); i++) {
                /* 获取文章信息 */
                article = new Article();
                article.setId(rs.get(i).getId());
                article.setTypename(rs.get(i).getTypename());
                article.setTitle(rs.get(i).getTitle());
                article.setContent(rs.get(i).getContent());
                article.setOrigin(rs.get(i).getOrigin());
                article.setPubtime(rs.get(i).getPubtime());
                article.setCount(rs.get(i).getCount());
//                article.setRemark(rs.get(i).getRemark());
                /* 查询tb_review数据表统计当前文章的评论数 */
                sql = "select count(id) from remark where articleid=" + article.getId();
                QueryRunner runner1 = new QueryRunner(DButil.getDataSource());
                Long c=(Long) runner1.query(sql,new ScalarHandler());
                int count=c.intValue();
                if (count != 0) {
                    article.setRemark(count);
                }
            }
        }
        return article;
    }
    //获取文章的发布时间
    public Date findPubTime(String title) throws SQLException {
        String sql="select pubtime from article where title=?";
        QueryRunner runner=new QueryRunner(DButil.getDataSource());
        Date time= (Date) runner.query(sql,new ScalarHandler(),title);;
        return time;

    }

    //按照阅读量获取文章
    public List<Article> findMaxCount() throws SQLException {
        String sql="SELECT * from article order by `count` desc";
        QueryRunner runner=new QueryRunner(DButil.getDataSource());
        return runner.query(sql, new BeanListHandler<Article>(Article.class));

    }


}
